﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using FFS.Model;
using FFS.IDAL;
using FFS.DBUtility;

namespace FFS.SQLDAL
{
    public class SysParameterDAO:ISysParameterDAO
    {
        private const string SYS_PARAM_ID = "@SysParamID";
        private const string SYS_PARAM_CATEGORY = "@SysParamCategory";
        private const string SYS_PARAM_NAME = "@SysParamName";
        private const string SYS_PARAM_TYPE = "@SysParamType";
        private const string SYS_PARAM_VALUE = "@SysParamValue";
        private const string SYS_PARAM_REMARK = "@SysParamRemark";
        private const string INPUT_USER = "@input_user";
        private const string SELECT_ALL_SYS_PARAM = @"SELECT [SysParamID]
                                                          ,[SysParamName]
                                                          ,[SysParamCategory]
                                                          ,[SysParamType]
                                                          ,[SysParamValue]
                                                          ,[SysParamRemark]
                                                          ,[input_date]
                                                          ,[input_user]
                                                      FROM [dbo].[SysParameter] 
                                                      where [SysParameter].is_deleted = 0";
        private const string INSERT_SYS_PARAM = @"  
                                                    INSERT INTO [dbo].[SysParameter]
                                                               ([SysParamName]
                                                               ,[SysParamCategory]
                                                               ,[SysParamType]
                                                               ,[SysParamValue]
                                                               ,[SysParamRemark]
                                                               ,[is_deleted]
                                                               ,[input_date]
                                                               ,[input_user])
                                                      values(@SysParamName,@SysParamCategory,@SysParamType,@SysParamValue,@SysParamRemark,0,getdate(),@input_user)";
        private const string UPDATE_SYS_PARAM = @" UPDATE SysParameter 
                                                     set  SysParamCategory = @SysParamCategory,
                                                          SysParamType = @SysParamType,
                                                          SysParamValue = @SysParamValue,
                                                          SysParamRemark = @SysParamRemark,
                                                          input_date = getdate(),
                                                          input_user = @input_user 
                                                    where SysParamName = @SysParamName and is_deleted = 0 ";
        private const string DELETE_SYS_PARAM = @"  UPDATE SysParameter 
                                                       set is_deleted = 1,input_date = getdate(),input_user = @input_user 
                                                    where is_deleted = 0 and SysParamName = @SysParamName 
                                                      ";

        private List<SysParameterInfo> GetQueryList(CommandType cmdType, string sqlText, params SqlParameter[] param)
        {
            List<SysParameterInfo> param_list = new List<SysParameterInfo>();

            using (SqlDataReader reader = DBHelper.ExecuteReader(cmdType, sqlText, param))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        SysParameterInfo sysparameter = new SysParameterInfo();
                        sysparameter.SysParamID = reader.GetInt32(0);
                        sysparameter.SysParamName = reader.GetString(1);
                        sysparameter.SysParamCategory = reader.GetString(2);
                        sysparameter.SysParamType = reader.GetString(3);
                        sysparameter.SysParamValue = reader.GetString(4);
                        sysparameter.SysParamRemark = reader.GetString(5);
                        sysparameter.Input_Date = reader.GetDateTime(6);
                        sysparameter.Input_User = reader.GetString(7);
                        param_list.Add(sysparameter);
                    }
                }
            }

            return param_list;
        }


        #region ISysParameterDAO 成员

        public List<SysParameterInfo> GetAllSysParam()
        {
            string sqlText = SELECT_ALL_SYS_PARAM;
            return this.GetQueryList(CommandType.Text, sqlText, null);
        }

        public List<SysParameterInfo> GetSysParamBySearchCondition(string searchCondition)
        {
            string sqlText = SELECT_ALL_SYS_PARAM + " and " + searchCondition;
            return this.GetQueryList(CommandType.Text,sqlText,null);
        }

        public int CreateSysParam(SysParameterInfo sysparam)
        {
            SqlParameter[] param = { 
                                     new SqlParameter(SYS_PARAM_CATEGORY,sysparam.SysParamCategory),
                                     new SqlParameter(SYS_PARAM_NAME,sysparam.SysParamName),
                                     new SqlParameter(SYS_PARAM_TYPE,sysparam.SysParamType),
                                     new SqlParameter(SYS_PARAM_VALUE,sysparam.SysParamValue),
                                     new SqlParameter(SYS_PARAM_REMARK,sysparam.SysParamRemark),
                                     new SqlParameter(INPUT_USER,sysparam.Input_User)
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text,INSERT_SYS_PARAM,param);
        }

        public int ModifySysParam(SysParameterInfo sysparam)
        {
            SqlParameter[] param = { 
                                     new SqlParameter(SYS_PARAM_CATEGORY,sysparam.SysParamCategory),
                                     new SqlParameter(SYS_PARAM_NAME,sysparam.SysParamName),
                                     new SqlParameter(SYS_PARAM_TYPE,sysparam.SysParamType),
                                     new SqlParameter(SYS_PARAM_VALUE,sysparam.SysParamValue),
                                     new SqlParameter(SYS_PARAM_REMARK,sysparam.SysParamRemark),
                                     new SqlParameter(INPUT_USER,sysparam.Input_User)
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text, UPDATE_SYS_PARAM, param);
        }

        public int DeleteSysParam(SysParameterInfo sysparam)
        {
            SqlParameter[] param = { 
                                        new SqlParameter(SYS_PARAM_NAME,sysparam.SysParamName),
                                        new SqlParameter(INPUT_USER,sysparam.Input_User)
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text,DELETE_SYS_PARAM,param);
        }

        public bool IsExistsSysParam(string paramName)
        {
            string sqlText = SELECT_ALL_SYS_PARAM + " and SysParamName = N'" + paramName.Trim() + "'";
            if (DBHelper.ExecuteScalar(CommandType.Text, sqlText, null) == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        #endregion
    }
}
